Knowledge base

Working with record files

One of the supported formats in PRODBX is the record file. This is a ASCII oriented 'flat' file. Typical in the record file structure is that the fields are identified, and the place of the fields can be random. (This is different with the field delimited files where the fields are written at a specified sequence in the record.

Example of a FAQ file (frequently asked questions):

This is an export of a Lotus Notes content handling frequently asked questions. Each question (record in the FAQ table) is written over multiple lines in the export. The fields (Columns) in each record are: Subject, Keyword, Question and Answer. They are written in the 'field name' specification of the tables and table mapping section. (The table name is always 'FILE')

To interpret the records PRODBX needs to know the constants used throughout the file:

• Column id starts with: this is the character or characters always written in front of a column (field). In our example nothing is written in front of them. But imagine your field names are written as [field], then the [ would be the start character of the field, and 5B would be the hexadecimal representation of this.
• Column id ends with: this is the character of characters always written after the column (field) name. In our example this is ':  '. The hexadecimal representation of this is 3A 20 20. Both the starting characters and end characters are not mandatory for PRODBX to interpret the file properly. PRODBX will always look for the occurence of the field name itself. If you do not provide end charachters, PRODBX will assume the data starts at the first character after the last field character.
• Line ends with: the character or characters written after the data to indicate the end of the data. This is mandatory information. If not provided PRODBX will read until the end of record separator. In our example these characters are not visible, because they are out of the range of visible ASCII characters. Here they are 0D 0A hexadecimal which is carriage return and line feed.
• Record ends with: manadatory information indicating the character or characters always ending a record. In our example this character is not visible since it is 0C hexadecimal, which is outside the range of visisble ASCII characters. It is mandatory to have the record end characters between each record, however your last record shouldn't necessarily end with it. PRODBX will automatically assume the end of file is also the end of the last record.

The big question with record files is: how do I determine the various characters when they aren't visible in a normal text editor, and how do I find the hexadecimal representation of them?

If you aren't familiar with hexadecimal values, or you don't have an editor in which you can view the invisible characters, there is still the old DOS debugger on your system. Call the MS-DOS prompt on your system and point the directory to the one where your record file is in. Use the command DEBUG filename. Thereafter you use the 'd' (dump) command to view the contents of your file in both hexadecimal and ASCII representation.

The file of our example will look like this:

When your file contains visible ASCII characters as 'separators' or you know the way your file is build from the documentation of your application exporting the file, and you want to determine the hexadecimal value, the next table could be of any help. The upper (heading) row gives you the first hexadecimal digit, the first (heading) column the second.

Remark: it may be that your file contains other control characters specifying font, links, ... To use these it is issue of applying the correct function to the data in your file. See the functions section. Please remind there are many standard software to find to convert text standards to other text standards, like converting RTF (Rich text file) to HTML (HyperText Make-up Language) (Visit http://www.logictran.com)